﻿Imports System.Data.OleDb


Public Class BaoCaoCongNo

    Public Function BaoCao(ByVal thang As String) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL 
        Dim sql As String
        Dim a As New OleDb.OleDbDataAdapter()
        If thang = "ALL" Then
            sql = "select TenKhachHang, TongTienNo + PhieuThu.SoTienThu TongTienNo , PhieuThu.SoTienThu , PhieuThu.NgayThu ,TongTienNo TienConNo ,  SoDienThoai , DiaChi  from PhieuThu, KhachHang   where PhieuThu.idKhachHang = KhachHang.IdKhachHang "
            Dim cmd As New OleDbCommand(sql, cn)
            a.SelectCommand = cmd
        Else

            Dim th As Integer = Integer.Parse(thang)
            sql = "select TenKhachHang, TongTienNo + PhieuThu.SoTienThu TongTienNo , PhieuThu.SoTienThu , PhieuThu.NgayThu ,TongTienNo TienConNo ,  SoDienThoai , DiaChi  from PhieuThu, KhachHang   where PhieuThu.idKhachHang = KhachHang.IdKhachHang and Month(PhieuThu.NgayThu) = ? "
            Dim cmd As New OleDbCommand(sql, cn)
            cmd.Parameters.Add("@thang", OleDbType.Integer)
            cmd.Parameters("@thang").Value = th
            a.SelectCommand = cmd
        End If
        'B4: Thuc thi chuoi strSQL 
        cn.Close()
        a.Fill(dt)
        Return dt
    End Function
End Class
